﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Yu.Cms.Common;
using Yu.Cms.Config;
using Yu.Enum;


namespace Yu.Cms.SQLServerDAL
{

    /// <summary> 
    /// 数据访问基础类(基于SQLServer)
    /// </summary>
    public abstract class DbHelperMsSQL
    {
        /// <summary>
        /// 获取数据库配置信息
        /// </summary>
        protected static string ConnectionString
        {
            get { return BaseConfigs.GetDBConnectString; }
        }

        /// <summary>
        /// 委托将DataReader转为实体类
        /// </summary>
        /// <param name="dr">记录集</param>
        /// <param name="Fileds">字段名列表</param>
        /// <returns></returns>
        public delegate T PopulateDelegate<T>(IDataReader dr, Dictionary<string, string> Fileds);

        public DbHelperMsSQL()
        {
        }

        #region "常用函数"

        #region "获取表中字段值"
        /// <summary>
        /// 获取表中字段值
        /// </summary>
        /// <param name="table_name">表名</param>
        /// <param name="table_fileds">字段</param>
        /// <param name="where_fileds">查询条件字段</param>
        /// <param name="where_value">查询值</param>
        /// <returns></returns>
        public string get_table_fileds(string table_name, string table_fileds, string where_fileds, string where_value)
        {            
            string rStr = "";
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                string strSql = string.Format("select {0} from {1} where upper({2})='{3}'", table_fileds, table_name, where_fileds, where_value);
                SqlCommand cmd = new SqlCommand(strSql, connection);
                cmd.CommandType = CommandType.Text;
                connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    rStr = dr[0].ToString();
                }
                dr.Close();
                dr.Dispose();
                cmd.Dispose();
                connection.Dispose();
                connection.Close();
            }
            return rStr;
        }
        #endregion

        #region "更新表中字段值"
        /// <summary>
        /// 更新表中字段值
        /// </summary>
        /// <param name="Table">表名</param>
        /// <param name="Table_FiledsValue">需要更新值(不用带Set)</param>
        /// <param name="Wheres">更新条件(不用带Where)</param>
        /// <returns></returns>
        public static int Update_Table_Fileds(string Table, string Table_FiledsValue, string Wheres)
        {
            int rInt = 0;
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                string strSql = string.Format("Update {0} Set {1}  Where {2}", Table, Table_FiledsValue, Wheres);
                SqlCommand cmd = new SqlCommand(strSql, connection);
                cmd.CommandType = CommandType.Text;
                connection.Open();
                rInt = cmd.ExecuteNonQuery();            
                cmd.Dispose();
                connection.Dispose();
                connection.Close();
            }
            return rInt;
        }
        #endregion


       
        /// <summary>
        /// 排序
        /// </summary>
        /// <param name="TableName">查询表名称</param>
        /// <param name="sort_fileds">排序字段</param>
        /// <param name="sortvalue">排序值（传入）</param>
        /// <param name="where_fileds">记录自动ID</param>
        /// <param name="where_values">记录自动ID值</param>
        /// <param name="other_files">附加条件字段</param>
        /// <param name="other_value">附加条件值</param>
        /// <param name="sort">排序类型</param>
        /// <returns>返回TRUE：排序成功</returns>
        public static bool SwitchSort(string TableName, string sort_fileds, int sortvalue, string where_fileds,int where_values, string other_files, int other_value, Sort sort)
        {
            int rInt = 0;
            string strSql = string.Empty;
            if (sort == Sort.up)
            {
                if (!string.IsNullOrEmpty(other_files))
                {
                    strSql = string.Format("select Top 1 * from {0} Where {1}<{2} And {3}={4} order by {1} desc", TableName, sort_fileds, sortvalue, other_files, other_value);
                }
                else
                {
                    strSql = string.Format("select Top 1 * from {0} Where {1}<{2} order by {1} desc", TableName, sort_fileds, sortvalue);
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(other_files))
                {
                    strSql = string.Format("select Top 1 * from {0} Where {1}>{2} And {3}={4} order by {1} asc", TableName, sort_fileds, sortvalue, other_files, other_value);
                }
                else
                {
                    strSql = string.Format("select Top 1 * from {0} Where {1}>{2} order by {1} asc", TableName, sort_fileds, sortvalue);
                }
            }
            DataSet ds = Query(strSql);
            if (ds.Tables[0].Rows.Count>0)
            {
                rInt = Update_Table_Fileds(TableName, sort_fileds + "=" + sortvalue, where_fileds + "=" + ds.Tables[0].Rows[0]["" + where_fileds + ""]);
                rInt = Update_Table_Fileds(TableName, sort_fileds + "=" + ds.Tables[0].Rows[0]["" + sort_fileds + ""], where_fileds + "=" + where_values);
            }
            if (rInt > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 公用方法

        #region "返回最大值+1"
        /// <summary>
        /// 返回最大值+1
        /// </summary>
        /// <param name="FieldName">字段名</param>
        /// <param name="TableName">字段值</param>
        /// <param name="Where">Where字段,(不用带Where)</param>
        /// <returns></returns>
        public static int GetMaxID(string TableName,string FieldName, string Where)
        {
           
            string strsql = string.Empty;
            if (!string.IsNullOrEmpty(Where))
            {
                strsql = string.Format("select max({0})+1 from {1} Where {2}", FieldName, TableName, Where); 
            }
            else
            {
                strsql = string.Format("select max({0})+1 from {1}", FieldName, TableName);
            }
           
            object obj = GetSingle(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }
        #endregion

        /// <summary>
        /// 总共记录数
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static int GetMaxCount(string TableName, string Where)
        {           
            string strSql = string.Empty;
            if (!string.IsNullOrEmpty(Where))
            {
                strSql = "select Count(1) from " + TableName + Where;
            }
            else
            {
               strSql = "select Count(1) from " + TableName;
            }
            object obj = GetSingle(strSql);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }

        
        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            object obj = GetSingle(strSql, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static bool Exists(string strSql)
        {
            object obj = GetSingle(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion

        #region  执行简单SQL语句

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public static void ExecuteSqlTran(ArrayList SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }



        /// <summary>
        /// 执行带一个存储过程参数的的SQL语句。
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, string content)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(SQLString, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(strSQL, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
                myParameter.Value = fs;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }


        /// <summary>
        /// 执行查询语句，返回SqlDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string strSQL)
        {
            SqlConnection connection = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                SqlDataReader myReader = cmd.ExecuteReader();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }

        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }



        /// <summary>
        /// 通过页大小，当前页数返回DataSet数据源
        /// </summary>
        /// <param name="int_PageSize">一页记录数</param>
        /// <param name="int_CurrentPageIndex">当前页数</param>
        /// <param name="SQLString">SQl语句</param>
        /// <returns>表示层传递过来的条件字段参数</returns>
        public static DataSet GetPageList(int int_PageSize, int int_CurrentPageIndex, string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);

                    if (int_PageSize == 0 && int_CurrentPageIndex == 0)
                    {
                        command.Fill(ds, "12news1234567890");
                    }
                    else
                    {
                        int int_Page = int_PageSize * (int_CurrentPageIndex - 1);
                        if (int_Page < 0)
                        {
                            int_Page = 0;
                        }
                        command.Fill(ds, int_Page, int_PageSize, "12news1234567890");
                    }
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }


        #endregion

        #region 通过SQL语句获得IList数据源

      
        
        /// <summary>
        /// 通过运行Sql语句获得IList数据源
        /// </summary>
        /// <param name="str_Sql">SQL语句</param>
        /// <param name="RecordCount">返回记录数</param>
        /// <returns></returns>
        public static IList GetIList(string str_Sql, out int RecordCount)
        {
            // ＝＝＝获得数据库源，返回IList为数据源＝＝＝
            IList Ilst = new ArrayList();
            RecordCount = 0;
            // 当没有传递条件参数时作的操作
            using (DataSet ds = Query(str_Sql))
            {
                DataTable dt = ds.Tables[0];
                //ArrayList o_Instance=new ArrayList();
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    Ilst.Add(dt.Rows[j]);
                    RecordCount = dt.Rows.Count;
                }
            }
            return Ilst;
        }
      
      

        /// <summary>
        ///   通过运行Sql语句获得IList数据源
        /// </summary>
        /// <param name="int_PageSize">页面</param>
        /// <param name="int_CurrentPageIndex">当前页</param>
        /// <param name="str_Sql">SQL语句</param>
        /// <param name="RecordCount">返回记录数</param>
        /// <returns></returns>
        public static IList GetPageIList(int int_PageSize, int int_CurrentPageIndex, string str_Sql, out int RecordCount)
        {
            // ＝＝＝获得数据库源，返回IList为数据源＝＝＝
            IList Ilst = new ArrayList();
            RecordCount = 0;
            // 当没有传递条件参数时作的操作
            using (DataSet ds = GetPageList(int_PageSize, int_CurrentPageIndex, str_Sql))
            {
                DataTable dt = ds.Tables[0];
                //ArrayList o_Instance=new ArrayList();
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    Ilst.Add(dt.Rows[j]);
                    RecordCount = dt.Rows.Count;
                }
            }
            return Ilst;
        }




        //存储过程版，暂时不用
        //public static IList RunSql(string conn_Default, int int_PageSize, int int_CurrentPageIndex, string procName, SqlParameter[] prams, string class_Name)
        //{
        //    // ＝＝＝获得数据库源，返回IList为数据源＝＝＝
        //    IList Ilst = new ArrayList();
        //    // 当没有传递条件参数时作的操作
        //    using (DataSet ds = GetPageList(int_PageSize, int_CurrentPageIndex, conn_Default, CommandType.StoredProcedure, procName, prams))
        //    {
        //        DataTable dt = ds.Tables[0];
        //        for (int j = 0; j < dt.Rows.Count; j++)
        //        {

        //            Type myType = Type.GetType(class_Name);// 获得“类”类型
        //            Object o_Instance = System.Activator.CreateInstance(myType); // 实例化类
        //            // 获得类的所有属性数组
        //            PropertyInfo[] myPropertyInfo1 = myType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
        //            // 循环属性数组，并给数组属性赋值
        //            for (int k = 0; k < myPropertyInfo1.Length; k++)
        //            {
        //                PropertyInfo myPropInfo = (PropertyInfo)myPropertyInfo1[k];
        //                Object filed_Val = dt.Rows[j][myPropInfo.Name];
        //                switch (myPropInfo.PropertyType.ToString())
        //                {
        //                    case "System.Int32":
        //                        myPropInfo.SetValue(o_Instance, (int)filed_Val, null);
        //                        break;
        //                    case "System.String":
        //                        myPropInfo.SetValue(o_Instance, filed_Val.ToString(), null);
        //                        break;
        //                    case "System.DateTime":
        //                        myPropInfo.SetValue(o_Instance, Convert.ToDateTime(filed_Val.ToString()), null);
        //                        break;
        //                }

        //            }
        //            // 把一行类记录赋值给ILst对象
        //            Ilst.Add(o_Instance);

        //        }
        //    }
        //    return Ilst;
        //}

        /**/
        /// <summary>
        /// 通过页大小，当前页数返回IList数据源
        /// </summary>
        /// <param name="int_PageSize">一页记录数</param>
        /// <param name="int_CurrentPageIndex">当前页数</param>
        /// <param name="Sql_Sel_Code">SQl语句</param>
        /// <param name="ht">传递条件哈希表</param>
        /// <param name="class_Name">实体类名</param>
        /// <returns>表示层传递过来的条件字段参数</returns>
        //public static IList GetPageList(string conn_Default, int int_PageSize, int int_CurrentPageIndex, string Table, string ht_Where, string orderby, Hashtable ht, string class_Name)
        //{
        //    // ＝＝＝获得数据库源，返回IList为数据源＝＝＝
        //    IList Ilst = new ArrayList();

        //    if (ht == null)
        //    {
        //        // 当没有传递条件参数时作的操作
        //        string str_Sql = GetPageListSqlbyHt(Table, ht_Where, orderby, null, class_Name);
        //        using (DataSet ds = ExecuteDataSet(int_PageSize, int_CurrentPageIndex, conn_Default, CommandType.Text, str_Sql, null))
        //        {
        //            DataTable dt = ds.Tables[0];
        //            for (int j = 0; j < dt.Rows.Count; j++)
        //            {

        //                Type myType = Type.GetType(class_Name);// 获得“类”类型
        //                Object o_Instance = System.Activator.CreateInstance(myType); // 实例化类
        //                // 获得类的所有属性数组
        //                PropertyInfo[] myPropertyInfo1 = myType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
        //                // 循环属性数组，并给数组属性赋值
        //                for (int k = 0; k < myPropertyInfo1.Length; k++)
        //                {
        //                    PropertyInfo myPropInfo = (PropertyInfo)myPropertyInfo1[k];

        //                    Object filed_Val = dt.Rows[j][myPropInfo.Name];
        //                    switch (myPropInfo.PropertyType.ToString())
        //                    {
        //                        case "System.Int32":
        //                            myPropInfo.SetValue(o_Instance, (int)filed_Val, null);
        //                            break;
        //                        case "System.String":
        //                            myPropInfo.SetValue(o_Instance, filed_Val.ToString(), null);
        //                            break;
        //                        case "System.DateTime":
        //                            myPropInfo.SetValue(o_Instance, Convert.ToDateTime(filed_Val.ToString()), null);
        //                            break;
        //                    }

        //                }
        //                // 把一行类记录赋值给ILst对象
        //                Ilst.Add(o_Instance);
        //            }
        //        }
        //    }
        //    else // 当没有传递条件参数时作的操作
        //    {

        //        // 处理传递过来的参数
        //        SqlParameter[] Parms = new SqlParameter[ht.Count];
        //        IDictionaryEnumerator et = ht.GetEnumerator();
        //        int i = 0;
        //        while (et.MoveNext())
        //        {
        //            System.Data.SqlClient.SqlParameter sp = MakeParam("@" + et.Key.ToString(), et.Value.ToString());
        //            Parms[i] = sp;
        //            i = i + 1;
        //        }
        //        string str_Sql = GetPageListSqlbyHt(Table, ht_Where, orderby, ht, class_Name);
        //        // 返回ILst
        //        using (DataSet ds = ExecuteDataSet(int_PageSize, int_CurrentPageIndex, conn_Default, CommandType.Text, str_Sql, Parms))
        //        {
        //            DataTable dt = ds.Tables[0];
        //            for (int j = 0; j < dt.Rows.Count; j++)
        //            {

        //                Type myType = Type.GetType(class_Name);// 获得“类”类型
        //                Object o_Instance = System.Activator.CreateInstance(myType); // 实例化类
        //                // 获得类的所有属性数组
        //                PropertyInfo[] myPropertyInfo1 = myType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
        //                // 循环属性数组，并给数组属性赋值
        //                for (int k = 0; k < myPropertyInfo1.Length; k++)
        //                {
        //                    PropertyInfo myPropInfo = (PropertyInfo)myPropertyInfo1[k];
        //                    Object filed_Val = dt.Rows[j][myPropInfo.Name];
        //                    switch (myPropInfo.PropertyType.ToString())
        //                    {
        //                        case "System.Int32":
        //                            myPropInfo.SetValue(o_Instance, (int)filed_Val, null);
        //                            break;
        //                        case "System.String":
        //                            myPropInfo.SetValue(o_Instance, filed_Val.ToString(), null);
        //                            break;
        //                        case "System.DateTime":
        //                            myPropInfo.SetValue(o_Instance, Convert.ToDateTime(filed_Val.ToString()), null);
        //                            break;
        //                    }

        //                }
        //                // 把一行类记录赋值给ILst对象




        //                Ilst.Add(o_Instance);

        //            }
        //        }

        //    }
        //    return Ilst;
        //}
      
        #endregion

        #region 公共查询数据函数Sql存储过程版
        /// <summary>
        /// 公共查询数据函数Sql存储过程版(非安全函数,传入参数请进行Sql字符串过滤)
        /// </summary>
        /// <param name="pd">委托对象</param>
        /// <param name="pp">查询字符串</param>
        /// <param name="RecordCount">返回记录总数</param>
        /// <returns>返回记录集List</returns>
        public static List<T> GetRecordListByProc<T>(PopulateDelegate<T> pd, QueryParam pp, out int RecordCount)
        {
            List<T> lst = new List<T>();
            RecordCount = 0;
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("dbo.SupesoftPage", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                // 设置参数
                cmd.Parameters.Add("@TableName", SqlDbType.NVarChar, 500).Value = pp.TableName;
                cmd.Parameters.Add("@ReturnFields", SqlDbType.NVarChar, 500).Value = pp.ReturnFields;
                cmd.Parameters.Add("@Where", SqlDbType.NVarChar, 500).Value = pp.Where;
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pp.PageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pp.PageSize;
                cmd.Parameters.Add("@Orderfld", SqlDbType.NVarChar, 200).Value = pp.Orderfld;
                cmd.Parameters.Add("@OrderType", SqlDbType.Int).Value = pp.OrderType;
                // 执行
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                Dictionary<string, string> Fileds = new Dictionary<string, string>();
                foreach (DataRow var in dr.GetSchemaTable().Select())
                {
                    Fileds.Add(var[0].ToString(), var[0].ToString());
                }
                while (dr.Read())
                {
                    lst.Add(pd(dr, Fileds));
                }
                // 取记录总数 及页数
                if (dr.NextResult())
                {
                    if (dr.Read())
                    {
                        RecordCount = Convert.ToInt32(dr["RecordCount"]);
                    }
                }

                dr.Close();
                cmd.Dispose();
                conn.Close();
            }
            return lst;
        }
        #endregion

        #region 公共查询数据函数Sql语句版本(非安全函数,传入参数请进行Sql字符串过滤)
       
        /// <summary>
        /// 公共查询数据函数Sql语句版本(非安全函数,传入参数请进行Sql字符串过滤)
        /// 超强的分页程序
        /// by 余海 2009-02-12
        /// </summary>
        /// <param name="pd">委托对象</param>
        /// <param name="pp">查询字符串</param>
        /// <param name="RecordCount">返回记录总数</param>
        /// <returns>返回记录集List</returns>
        public static IList<T> GetRecordListBySql<T>(PopulateDelegate<T> pd, QueryParam pp, out int RecordCount)
        {
            IList<T> lst = new List<T>();
            RecordCount = 0;
            using (SqlConnection Conn = new SqlConnection(ConnectionString))
            {
                StringBuilder sb = new StringBuilder();
                SqlCommand cmd = new SqlCommand();
                SqlDataReader dr = null;
                cmd.Connection = Conn;

                int TotalRecordForPageIndex = pp.PageIndex * pp.PageSize;
                string OrderBy;
                string CutOrderBy;
                if (pp.OrderType == 1)
                {
                    OrderBy = " Order by " + pp.Orderfld.Replace(",", " desc,") + " desc ";
                    CutOrderBy = " Order by " + pp.Orderfld.Replace(",", " asc,") + " asc ";
                }
                else
                {
                    OrderBy = " Order by " + pp.Orderfld.Replace(",", " asc,") + " asc ";
                    CutOrderBy = " Order by " + pp.Orderfld.Replace(",", " desc,") + " desc ";
                }

                Conn.Open();
                // 取记录总数
                cmd.CommandText = string.Format("SELECT Count(1) From {0} {1}", pp.TableName, pp.Where);
                RecordCount = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Parameters.Clear();

                int CurrentPageSize = pp.PageSize;
                if ((RecordCount - 1) / pp.PageSize + 1 == pp.PageIndex)
                {
                    CurrentPageSize = RecordCount % pp.PageSize;
                    if (CurrentPageSize == 0)
                        CurrentPageSize = pp.PageSize;
                }
                //取记录值
                //MYSQL : sb.AppendFormat("SELECT * FROM (SELECT * FROM (SELECT  {2}	FROM {3} {4} {5} LIMIT {1} ) TB2  {6}  LIMIT {0}) TB3 {5} ", CurrentPageSize, TotalRecordForPageIndex, pp.ReturnFields, pp.TableName, pp.Where, OrderBy, CutOrderBy);
                sb.AppendFormat("SELECT * FROM (SELECT TOP {0} * FROM (SELECT TOP {1} {2}	FROM {3} {4} {5}) TB2	{6}) TB3 {5} ", CurrentPageSize, TotalRecordForPageIndex, pp.ReturnFields, pp.TableName, pp.Where, OrderBy, CutOrderBy);
                cmd.CommandText = sb.ToString();
                dr = cmd.ExecuteReader();

                Dictionary<string, string> Fileds = new Dictionary<string, string>();
                foreach (DataRow var in dr.GetSchemaTable().Select())
                {
                    Fileds.Add(var[0].ToString(), var[0].ToString());
                }
                while (dr.Read())
                {
                    lst.Add(pd(dr, Fileds));
                }
                dr.Close();
                dr.Dispose();
                dr.Close();
                cmd.Dispose();
                Conn.Dispose();
                Conn.Close();
            }
            return lst;
        }
        #endregion
 

        #region 执行带参数的SQL语句



        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="cmdParms">指定参数</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        throw new Exception(E.Message);
                    }
                }
            }
        }


        /// <summary>
        /// 通过运行Sql语句获得IList数据源
        /// </summary>
        /// <param name="str_Sql">SQL语句</param>
        /// <param name="RecordCount">返回记录数</param>
        /// <returns></returns>
        public static IList GetIList(string SQLString, params SqlParameter[] cmdParms)
        {
            // ＝＝＝获得数据库源，返回IList为数据源＝＝＝
            IList Ilst = new ArrayList();         
            // 当没有传递条件参数时作的操作
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                    DataSet ds = Query(SQLString);
                    DataTable dt = ds.Tables[0];
                    //ArrayList o_Instance=new ArrayList();
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        Ilst.Add(dt.Rows[j]);
                    }
                }                     
            }
            return Ilst;
        }



       
        
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();

                            trans.Commit();
                        }
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }



     
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <param name="cmdParms">查询结果（object）</param>
        /// <returns></returns>
        public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                try
                {                   
                    PrepareCommand(cmd, connection, null, SQLString, cmdParms);                  
                    object obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SqlClient.SqlException e)
                {

                    throw new Exception(e.Message);
                }


            }
        }


        /// <summary>
        /// 执行查询语句，返回SqlDataReader
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <param name="cmdParms"></param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
        {
            SqlConnection connection = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                SqlDataReader myReader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }

        }



       
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <param name="cmdParms">cmdParms</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }


       
        /// <summary>
        /// 通过页大小，当前页数返回DataSet数据源
        /// </summary>
        /// <param name="int_PageSize">一页记录数</param>
        /// <param name="int_CurrentPageIndex">当前页数</param>
        /// <param name="SQLString">查询语句</param>
        /// <param name="cmdParms">SqlParameters to use in the command</param>
        /// <returns>返回DataSet数据源</returns>
        public static DataSet GetPageList(int int_PageSize, int int_CurrentPageIndex, string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {                
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        if (int_PageSize == 0 && int_CurrentPageIndex == 0)
                        {
                            da.Fill(ds, "12news1234567890");
                        }
                        else
                        {
                            int int_Page = int_PageSize * (int_CurrentPageIndex - 1);
                            if (int_Page < 0)
                            {
                                int_Page = 0;
                            }
                            da.Fill(ds, int_Page, int_PageSize, "12news1234567890");
                        }                      
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }               
            }
        }


        /// <summary>
        /// Prepare a command for execution
        /// </summary>
        /// <param name="cmd">SqlCommand object</param>
        /// <param name="conn">SqlConnection object</param>
        /// <param name="trans">SqlTransaction object</param>     
        /// <param name="cmdText">Command text, e.g. Select * from Products</param>
        /// <param name="cmdParms">SqlParameters to use in the command</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        #endregion

        #region 存储过程操作

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(ConnectionString);
            SqlDataReader returnReader;
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader();
            return returnReader;
        }


        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }


        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }

        /// <summary>
        /// 执行存储过程，返回影响的行数		
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                int result;
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                //Connection.Close();
                return result;
            }
        }

        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)	
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }
        #endregion


        #region PrepareCommand


        /// <summary>
        /// Prepare a command for execution
        /// </summary>
        /// <param name="cmd">SqlCommand object</param>
        /// <param name="conn">SqlConnection object</param>
        /// <param name="trans">SqlTransaction object</param>
        /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
        /// <param name="cmdText">Command text, e.g. Select * from Products</param>
        /// <param name="cmdParms">SqlParameters to use in the command</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParams)
        {
            if (cmd == null) throw new ArgumentNullException("command");
            if (cmdText == null || cmdText.Length == 0) throw new ArgumentNullException("commandText");

            // If the provided connection is not open, we will open it
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            // Associate the connection with the command
            cmd.Connection = conn;

            // Set the command text (stored procedure name or SQL statement)
            cmd.CommandText = cmdText;

            // If we were provided a transaction, assign it
            if (trans != null)
            {
                if (trans.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                cmd.Transaction = trans;
            }

            // Set the command type
            cmd.CommandType = cmdType;

            // Attach the command parameters if they are provided
            if (cmdParams != null)
            {
                AttachParameters(cmd, cmdParams);
            }
            return;
        }

        /// <summary>
        /// This method is used to attach array of SqlParameters to a SqlCommand.
        /// 
        /// This method will assign a value of DbNull to any parameter with a direction of
        /// InputOutput and a value of null.  
        /// 
        /// This behavior will prevent default values from being used, but
        /// this will be the less common case than an intended pure output parameter (derived as InputOutput)
        /// where the user provided no input value.
        /// </summary>
        /// <param name="command">The command to which the parameters will be added</param>
        /// <param name="commandParameters">An array of SqlParameters to be added to command</param>
        public static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (SqlParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        // Check for derived output value with no value assigned
                        if ((p.Direction == ParameterDirection.InputOutput ||
                            p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }

        #endregion

        #region Make SqlParameters
        /// <summary>
        /// Make input param.
        /// </summary>
        /// <param name="ParamName">Name of param.</param>
        /// <param name="DbType">Param type.</param>
        /// <param name="Size">Param size.</param>
        /// <param name="Value">Param value.</param>
        /// <returns>New parameter.</returns>
        public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }

        /// <summary>
        /// Make input param.
        /// </summary>
        /// <param name="ParamName">Name of param.</param>
        /// <param name="DbType">Param type.</param>
        /// <param name="Size">Param size.</param>
        /// <returns>New parameter.</returns>
        public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
        }

        /// <summary>
        /// Make stored procedure param.
        /// </summary>
        /// <param name="ParamName">Name of param.</param>
        /// <param name="DbType">Param type.</param>
        /// <param name="Size">Param size.</param>
        /// <param name="Direction">Parm direction.</param>
        /// <param name="Value">Param value.</param>
        /// <returns>New parameter.</returns>
        private static SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            SqlParameter param;

            if (Size > 0)
                param = new SqlParameter(ParamName, DbType, Size);
            else
                param = new SqlParameter(ParamName, DbType);

            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
                param.Value = Value;

            return param;
        }

        #endregion

    }

   
}
